package tn.softcom.dfbuilder;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import tn.softcom.dfbuilder.modele.ADJGCells;
import tn.softcom.dfbuilder.modele.ADJGCellsGroup;
import tn.softcom.dfbuilder.modele.ADJICells;
import tn.softcom.dfbuilder.modele.ADJICellsGroup;
import tn.softcom.dfbuilder.modele.ADJSCells;
import tn.softcom.dfbuilder.modele.ADJSCellsGroup;
import tn.softcom.dfbuilder.modele.Rapport;
import tn.softcom.dfbuilder.modele.RapportADJG;
import tn.softcom.dfbuilder.modele.RapportADJI;
import tn.softcom.dfbuilder.modele.Template;
import tn.softcom.dfbuilder.modele.TemplateADJG;
import tn.softcom.dfbuilder.modele.TemplateADJI;

public class EXCELComponent {
	private Workbook workbook;
	private Workbook workbookTemplate;
	private Workbook rapport;

	public static final String SOURCE = "CELL ID";
	public static final String DESTINATION = "NCELL ID";
	public static final String SEPARATOR = "F";
	public static final String SEPARATOR_0 = SEPARATOR + "0";
	public static final String SEPARATOR_1 = SEPARATOR + "1";
	public static final String SEPARATOR_2 = SEPARATOR + "2";
	public static final String TYPE_2G = "G900";
	public static final String TYPE_3G = "G1800";
	private List<Template> lstTemplate = new ArrayList<Template>();
	private List<TemplateADJG> lstTemplateADJG = new ArrayList<TemplateADJG>();
	private List<TemplateADJI> lstTemplateADJI = new ArrayList<TemplateADJI>();
	private List<ADJSCellsGroup> adjsAddGroup = new ArrayList<ADJSCellsGroup>();
	private List<ADJGCellsGroup> adjgAddGroup = new ArrayList<ADJGCellsGroup>();
	private List<ADJICellsGroup> adjiAddGroup = new ArrayList<ADJICellsGroup>();

	public EXCELComponent(Workbook w,  Workbook wtemp,Workbook wrapport) throws IOException {
		this.workbook = w;
		this.workbookTemplate = wtemp;
		this.rapport = wrapport;
	}

	public EXCELComponent() {
	}

	public EXCELComponent(Workbook w) throws IOException {
		this.workbook = w;
	}

	public void readSheet(String operation) {
		// ADJS
		Sheet sheet = workbook.getSheet("ADJS");
		if(sheet!=null){
			for (int i = 1; i < sheet.getLastRowNum(); i++) {
				Cell cellSource = sheet.getRow(i).getCell(0);
				Cell cellDestination = sheet.getRow(i).getCell(1);
				if(cellSource!=null && cellDestination != null){
					if(!new ADJSCells(cellSource.getStringCellValue(), cellDestination.getStringCellValue()).findIfRelationExists())
						designSourceDestination(cellSource.getStringCellValue(),cellDestination.getStringCellValue(),operation);
				}
			}
		}
		//ADJG
		Sheet sheetADJG = workbook.getSheet("ADJG");
		if(sheetADJG!=null){
			for (int i = 1; i < sheetADJG.getLastRowNum(); i++) {
				Cell cellSource = sheetADJG.getRow(i).getCell(0);
				Cell cellDestination = sheetADJG.getRow(i).getCell(1);
				if(cellSource!=null && cellDestination != null){
					if(!new ADJSCells(cellSource.getStringCellValue(), cellDestination.getStringCellValue()).findIfRelationExists()){
						designSourceDestinationADJG(cellSource.getStringCellValue(),cellDestination.getStringCellValue(),operation,TYPE_2G);
						if("New".equals(operation)){
							String destTemp = cellDestination.getStringCellValue().substring(0,3)+"3"+cellDestination.getStringCellValue().substring(4);
							designSourceDestinationADJG(cellSource.getStringCellValue(),destTemp,operation,TYPE_3G);
						}
					}
				}
			}
		}
		
		//ADJI
			Sheet sheetADJI = workbook.getSheet("ADJI");
			if(sheetADJI!=null){
				for (int i = 1; i < sheetADJI.getLastRowNum(); i++) {
					Cell cellSource = sheetADJI.getRow(i).getCell(0);
					Cell cellDestination = sheet.getRow(i).getCell(1);
					if(cellSource!=null && cellDestination != null){
						if(!new ADJICells(cellSource.getStringCellValue(), cellDestination.getStringCellValue()).findIfRelationExists())
							designSourceDestinationADJI(cellSource.getStringCellValue(),cellDestination.getStringCellValue(),operation);
					}
				}
			}
	}
	
	public void readTemplate() {
		//ADJS
		Sheet sheet = workbookTemplate.getSheet("ADJS");
		if(sheet!=null){
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Template template = new Template();
				template.setAdjsDERR((int)(sheet.getRow(i).getCell(1).getNumericCellValue()));
				template.setAdjsEcNoOffset((int)(sheet.getRow(i).getCell(2).getNumericCellValue()));
				template.setAdjsTxPwrRACH((int)(sheet.getRow(i).getCell(3).getNumericCellValue()));
				template.setADJSChangeOrigin((int)(sheet.getRow(i).getCell(4).getNumericCellValue()));
				template.setRtHopsIdentifier((int)(sheet.getRow(i).getCell(5).getNumericCellValue()));
				template.setNrtHopsIdentifier((int)(sheet.getRow(i).getCell(6).getNumericCellValue()));
				template.setHSDPAHopsIdentifier((int)(sheet.getRow(i).getCell(7).getNumericCellValue()));
				template.setRTWithHSDPAHopsIdentifier((int)(sheet.getRow(i).getCell(8).getNumericCellValue()));
				template.setName((sheet.getRow(i).getCell(9).getStringCellValue()));
				lstTemplate.add(template);		
			}
			System.out.println(lstTemplate);
		}
		
		//ADJG
		Sheet sheetADJG = workbookTemplate.getSheet("ADJG");
		if(sheetADJG!=null){
			for (int i = 1; i <= sheetADJG.getLastRowNum(); i++) {
				TemplateADJG template = new TemplateADJG();
				template.setAdjgMCC((getCellValue(sheetADJG.getRow(i).getCell(6))));
				template.setAdjgMNC((getCellValue(sheetADJG.getRow(i).getCell(7))));
				template.setADJGChangeOrigin((getCellValue(sheetADJG.getRow(i).getCell(8))));
				template.setADJGType((getCellValue(sheetADJG.getRow(i).getCell(9))));
				template.setAdjgBandIndicator((getCellValue(sheetADJG.getRow(i).getCell(12))));
				template.setAdjgTxPwrMaxRACH((getCellValue(sheetADJG.getRow(i).getCell(17))));
				template.setAdjgTxPwrMaxTCH((getCellValue(sheetADJG.getRow(i).getCell(18))));
				template.setNrtHopgIdentifier((getCellValue(sheetADJG.getRow(i).getCell(19))));
				template.setRtHopgIdentifier((getCellValue(sheetADJG.getRow(i).getCell(20))));
				template.setName((sheetADJG.getRow(i).getCell(21).getStringCellValue()));
				lstTemplateADJG.add(template);		
			}
			System.out.println(lstTemplateADJG);
		}
		
		//ADJI
			Sheet sheetADJI = workbookTemplate.getSheet("ADJI");
			if(sheetADJI!=null){
				for (int i = 1; i <= sheetADJI.getLastRowNum(); i++) {
					TemplateADJI template = new TemplateADJI();
					template.setAdjiCPICHTxPwr((getCellValue(sheetADJI.getRow(i).getCell(6))));
					template.setAdjiTxDiv((getCellValue(sheetADJI.getRow(i).getCell(14))));
					template.setAdjiTxPwrDPCH((getCellValue(sheetADJI.getRow(i).getCell(15))));
					template.setAdjiTxPwrRACH((getCellValue(sheetADJI.getRow(i).getCell(16))));
					template.setNrtHopiIdentifier((getCellValue(sheetADJI.getRow(i).getCell(18))));
					template.setRtHopiIdentifier((getCellValue(sheetADJI.getRow(i).getCell(19))));
					template.setAdjiComLoadMeasDRNCCellNCHO((getCellValue(sheetADJI.getRow(i).getCell(20))));
					template.setAdjiHandlingBlockedCellSLHO((getCellValue(sheetADJI.getRow(i).getCell(21))));
					template.setAdjiNCHOHSPASupport((getCellValue(sheetADJI.getRow(i).getCell(22))));
					template.setBlindHOTargetCell((getCellValue(sheetADJI.getRow(i).getCell(23))));
					template.setName((sheetADJI.getRow(i).getCell(24).getStringCellValue()));
					lstTemplateADJI.add(template);		
				}
				System.out.println(lstTemplateADJI);
			}
	}
	public File writeSheet(HttpServletRequest request) throws FileNotFoundException, IOException{
		File out = new File("output.xls");
		
		Workbook wworkbook = new HSSFWorkbook();
		

		Sheet wsheet = wworkbook.createSheet("ADJS ADD");
		Sheet wsheetMax = wworkbook.createSheet("ADJS MAX");
		Sheet  wsheeToDelete = wworkbook.createSheet("Adjs To Delete");
		
		Sheet wsheetADJG = wworkbook.createSheet("ADJG ADD");
		Sheet wsheetMaxADJG = wworkbook.createSheet("ADJG MAX");
		Sheet  wsheeToDeleteADJG = wworkbook.createSheet("ADJG To Delete");
		
		Sheet wsheetADJI = wworkbook.createSheet("ADJI ADD");
		Sheet wsheetMaxADJI = wworkbook.createSheet("ADJI MAX");
		Sheet  wsheeToDeleteADJI = wworkbook.createSheet("ADJI To Delete");
		
		// ADJS
		int i = 0;
		int j = 0;
		
		for (ADJSCellsGroup group : adjsAddGroup) {
			group.setADJSID();
			
			for (ADJSCells adjs : group.getLstADJS()) {
				applyTemplate(adjs);
				if(!"MAX".equals(adjs.getADJSId())){
					designExcelSheet(adjs, wsheet, i,wworkbook);
					i++;
				}
			}
			// ADJS Max atteint design new sheet
			if(group.getLstADJSFailToGetID()!=null && group.getLstADJSFailToGetID().size()>0){
				readRapports(group);
				for (ADJSCells adjs : group.getLstADJSFailToGetID()) {
					applyTemplate(adjs);
					Rapport rapportToRetreive = group.retreiveNewId();
					adjs.setADJSId(rapportToRetreive.getIdToRetreve());
					designExcelSheet(adjs, wsheetMax, j,wworkbook);
					designExcelSheetToDelete(adjs, wsheeToDelete,  j,rapportToRetreive,request,wworkbook);
					j++;
				}
			}
		}
		
		// ADJG
				i = 0;
				j = 0;
				
				for (ADJGCellsGroup group : adjgAddGroup) {
					group.setADJGID();
					
					for (ADJGCells adjg : group.getLstADJG()) {
						applyTemplateADJG(adjg);
						if(!"MAX".equals(adjg.getADJGId())){
							designExcelSheetADJG(adjg, wsheetADJG, i,wworkbook);
							i++;
						}
					}
//					// ADJG Max atteint design new sheet
					if(group.getLstADJGFailToGetID()!=null && group.getLstADJGFailToGetID().size()>0){
						readRapportsADJG(group);
						for (ADJGCells adjg : group.getLstADJGFailToGetID()) {
							applyTemplateADJG(adjg);
							RapportADJG rapportToRetreive = group.retreiveNewId();
							adjg.setADJGId(rapportToRetreive.getIdToRetreve());
							designExcelSheetADJG(adjg, wsheetMaxADJG, j,wworkbook);
							designExcelSheetToDeleteADJG(adjg, wsheeToDeleteADJG,  j,rapportToRetreive,request,wworkbook);
							j++;
						}
					}
				}
				
		//ADJI
				 i = 0;
				 j = 0;
				
				for (ADJICellsGroup group : adjiAddGroup) {
					group.setADJIID();
					
					for (ADJICells adji : group.getLstADJI()) {
						applyTemplateADJI(adji);
						if(!"MAX".equals(adji.getADJIId())){
							designExcelSheetADJI(adji, wsheetADJI, i,wworkbook);
							i++;
						}
					}
					// ADJI Max atteint design new sheet
					if(group.getLstADJIFailToGetID()!=null && group.getLstADJIFailToGetID().size()>0){
						readRapportsADJI(group);
						for (ADJICells adji : group.getLstADJIFailToGetID()) {
							applyTemplateADJI(adji);
							RapportADJI rapportToRetreive = group.retreiveNewId();
							adji.setADJIId(rapportToRetreive.getIdToRetreve());
							designExcelSheetADJI(adji, wsheetMaxADJI, j,wworkbook);
							designExcelSheetToDeleteADJI(adji, wsheeToDeleteADJI,  j,rapportToRetreive,request,wworkbook);
							j++;
						}
					}
				}
		
		wworkbook.write(new FileOutputStream(out));
		wworkbook.close();
		return out;
	}
	private void designSourceDestinationADJI(String cellSource,	String cellDestination,String operation) {
		if("New".equalsIgnoreCase(operation)){
			if (cellSource.contains(SEPARATOR_0) || cellSource.contains(SEPARATOR_1)) {
				ADJICells adji = new ADJICells(cellSource.replace(SEPARATOR_1, SEPARATOR_0),cellDestination.replace(SEPARATOR_0, SEPARATOR_1));
				adji.setParameter(SEPARATOR_0 + "_" +SEPARATOR_1 + "_ADJI");
				addToAdjiGroup(adji);
	
				adji = new ADJICells(cellSource.replace(SEPARATOR_0, SEPARATOR_1),cellDestination.replace(SEPARATOR_1, SEPARATOR_0));
				adji.setParameter(SEPARATOR_1 + "_" +SEPARATOR_0 + "_ADJI");
				addToAdjiGroup(adji);	
			}
		}
	}
	private void designSourceDestination(String cellSource,
			String cellDestination,String operation) {
		if("New".equalsIgnoreCase(operation)){
			if (cellSource.contains(SEPARATOR_0)) {
				ADJSCells adjs = new ADJSCells(cellSource, cellDestination);
				adjs.setParameter(SEPARATOR_0 + "_ADJS");
				addToAdjsGroup(adjs);
	
				adjs = new ADJSCells(cellSource.replace(SEPARATOR_0, SEPARATOR_1),
						cellDestination.replace(SEPARATOR_0, SEPARATOR_1));
				adjs.setParameter(SEPARATOR_1 + "_ADJS");
				addToAdjsGroup(adjs);
	
				adjs = new ADJSCells(cellSource.replace(SEPARATOR_0, SEPARATOR_2),
						cellDestination.replace(SEPARATOR_0, SEPARATOR_2));
				adjs.setParameter(SEPARATOR_2 + "_ADJS");
				addToAdjsGroup(adjs);
			} else if (cellSource.contains(SEPARATOR_1)) {
				ADJSCells adjs = new ADJSCells(cellSource, cellDestination);
				adjs.setParameter(SEPARATOR_1 + "_ADJS");
				addToAdjsGroup(adjs);
	
				adjs = new ADJSCells(cellSource.replace(SEPARATOR_1, SEPARATOR_0),
						cellDestination.replace(SEPARATOR_1, SEPARATOR_0));
				adjs.setParameter(SEPARATOR_0 + "_ADJS");
				addToAdjsGroup(adjs);
	
				adjs = new ADJSCells(cellSource.replace(SEPARATOR_1, SEPARATOR_2),
						cellDestination.replace(SEPARATOR_1, SEPARATOR_2));
				adjs.setParameter(SEPARATOR_2 + "_ADJS");
				addToAdjsGroup(adjs);
			} else if (cellSource.contains(SEPARATOR_2)) {
				ADJSCells adjs = new ADJSCells(cellSource, cellDestination);
				adjs.setParameter(SEPARATOR_2 + "_ADJS");
				addToAdjsGroup(adjs);
	
				adjs = new ADJSCells(cellSource.replace(SEPARATOR_2, SEPARATOR_0),
						cellDestination.replace(SEPARATOR_2, SEPARATOR_0));
				adjs.setParameter(SEPARATOR_0 + "_ADJS");
				addToAdjsGroup(adjs);
	
				adjs = new ADJSCells(cellSource.replace(SEPARATOR_2, SEPARATOR_1),
						cellDestination.replace(SEPARATOR_2, SEPARATOR_1));
				adjs.setParameter(SEPARATOR_1 + "_ADJS");
				addToAdjsGroup(adjs);
			}
		}
		else{
			ADJSCells adjs = new ADJSCells(cellSource, cellDestination);
			if (cellSource.contains(SEPARATOR_0)) 
				adjs.setParameter(SEPARATOR_0 + "_ADJS");
			else if(cellSource.contains(SEPARATOR_1))
				adjs.setParameter(SEPARATOR_1 + "_ADJS");
			else if(cellSource.contains(SEPARATOR_2))
				adjs.setParameter(SEPARATOR_2 + "_ADJS");
			addToAdjsGroup(adjs);
		}

	}
	private void designSourceDestinationADJG(String cellSource,String cellDestination,String operation,String type) {
		if("New".equalsIgnoreCase(operation)){
			if (cellSource.contains(SEPARATOR_0)) {
				ADJGCells ADJG = new ADJGCells(cellSource, cellDestination);
				ADJG.setParameter(SEPARATOR_0 +  "_" + type);
				addToAdjGGroup(ADJG);
	
				ADJG = new ADJGCells(cellSource.replace(SEPARATOR_0, SEPARATOR_1),
						cellDestination.replace(SEPARATOR_0, SEPARATOR_1));
				ADJG.setParameter(SEPARATOR_1 + "_" + type);
				addToAdjGGroup(ADJG);
	
				ADJG = new ADJGCells(cellSource.replace(SEPARATOR_0, SEPARATOR_2),
						cellDestination.replace(SEPARATOR_0, SEPARATOR_2));
				ADJG.setParameter(SEPARATOR_2 +  "_" + type);
				addToAdjGGroup(ADJG);
			} else if (cellSource.contains(SEPARATOR_1)) {
				ADJGCells ADJG = new ADJGCells(cellSource, cellDestination);
				ADJG.setParameter(SEPARATOR_1 + "_" + type);
				addToAdjGGroup(ADJG);
	
				ADJG = new ADJGCells(cellSource.replace(SEPARATOR_1, SEPARATOR_0),
						cellDestination.replace(SEPARATOR_1, SEPARATOR_0));
				ADJG.setParameter(SEPARATOR_0 +  "_" + type);
				addToAdjGGroup(ADJG);
	
				ADJG = new ADJGCells(cellSource.replace(SEPARATOR_1, SEPARATOR_2),
						cellDestination.replace(SEPARATOR_1, SEPARATOR_2));
				ADJG.setParameter(SEPARATOR_2 + "_" + type);
				addToAdjGGroup(ADJG);
			} else if (cellSource.contains(SEPARATOR_2)) {
				ADJGCells ADJG = new ADJGCells(cellSource, cellDestination);
				ADJG.setParameter(SEPARATOR_2 + "_" + type);
				addToAdjGGroup(ADJG);
	
				ADJG = new ADJGCells(cellSource.replace(SEPARATOR_2, SEPARATOR_0),
						cellDestination.replace(SEPARATOR_2, SEPARATOR_0));
				ADJG.setParameter(SEPARATOR_0 +  "_" + type);
				addToAdjGGroup(ADJG);
	
				ADJG = new ADJGCells(cellSource.replace(SEPARATOR_2, SEPARATOR_1),
						cellDestination.replace(SEPARATOR_2, SEPARATOR_1));
				ADJG.setParameter(SEPARATOR_1 +  "_" + type);
				addToAdjGGroup(ADJG);
			}
		}
		else{
			ADJGCells adjg = new ADJGCells(cellSource, cellDestination);
			if (cellSource.contains(SEPARATOR_0)) 
				adjg.setParameter(SEPARATOR_0 +  "_" + type);
			else if(cellSource.contains(SEPARATOR_1))
				adjg.setParameter(SEPARATOR_1 +  "_" + type);
			else if(cellSource.contains(SEPARATOR_2))
				adjg.setParameter(SEPARATOR_2 + "_" + type);
			addToAdjGGroup(adjg);
		}

	}
	private void addToAdjsGroup(ADJSCells adjsToAdd) {
		if (adjsAddGroup != null && adjsAddGroup.size() > 0) {
			boolean exist = false;
			int index = 0;
			for (ADJSCellsGroup group : adjsAddGroup) {
				if (group.getSource().equals(adjsToAdd.getSource())) {
					exist = true;
					break;
				}
				index++;
			}
			if (exist) {
				adjsAddGroup.get(index).addADJS(adjsToAdd);
			} else {
				ADJSCellsGroup groupe = new ADJSCellsGroup(adjsToAdd.getSource());
				groupe.addADJS(adjsToAdd);
				adjsAddGroup.add(groupe);
			}
		} else {
			adjsAddGroup = new ArrayList<ADJSCellsGroup>();
			ADJSCellsGroup groupe = new ADJSCellsGroup(adjsToAdd.getSource());
			groupe.addADJS(adjsToAdd);
			adjsAddGroup.add(groupe);
		}
	}
	
	private void addToAdjiGroup(ADJICells adjiToAdd) {
		if (adjiAddGroup != null && adjiAddGroup.size() > 0) {
			boolean exist = false;
			int index = 0;
			for (ADJICellsGroup group : adjiAddGroup) {
				if (group.getSource().equals(adjiToAdd.getSource())) {
					exist = true;
					break;
				}
				index++;
			}
			if (exist) {
				adjiAddGroup.get(index).addADJI(adjiToAdd);
			} else {
				ADJICellsGroup groupe = new ADJICellsGroup(adjiToAdd.getSource());
				groupe.addADJI(adjiToAdd);
				adjiAddGroup.add(groupe);
			}
		} else {
			adjiAddGroup = new ArrayList<ADJICellsGroup>();
			ADJICellsGroup groupe = new ADJICellsGroup(adjiToAdd.getSource());
			groupe.addADJI(adjiToAdd);
			adjiAddGroup.add(groupe);
		}
	}
	private void addToAdjGGroup(ADJGCells adjgToAdd) {
		if (adjgAddGroup != null && adjgAddGroup.size() > 0) {
			boolean exist = false;
			int index = 0;
			for (ADJGCellsGroup group : adjgAddGroup) {
				if (group.getSource().equals(adjgToAdd.getSource())) {
					exist = true;
					break;
				}
				index++;
			}
			if (exist) {
				adjgAddGroup.get(index).addADJG(adjgToAdd);
			} else {
				ADJGCellsGroup groupe = new ADJGCellsGroup(adjgToAdd.getSource());
				groupe.addADJG(adjgToAdd);
				adjgAddGroup.add(groupe);
			}
		} else {
			adjgAddGroup = new ArrayList<ADJGCellsGroup>();
			ADJGCellsGroup groupe = new ADJGCellsGroup(adjgToAdd.getSource());
			groupe.addADJG(adjgToAdd);
			adjgAddGroup.add(groupe);
		}
	}
	private void designExcelSheetHeader(Sheet wsheet,int i,Workbook wb) {
		if(i == 0){ // design header
			Row header = wsheet.createRow(0);
			Font headerFont = wb.createFont();
		    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		    CellStyle headerStyle = wb.createCellStyle();
		    headerStyle.setFont(headerFont);
		    headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		    headerStyle.setRotation((short) 90);
		    headerFont.setColor(IndexedColors.WHITE.getIndex());
		    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			int j = 0;
		    header.createCell(j).setCellValue("CELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("NCELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Parameter");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("PLMN");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RncId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WBTSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("LcrId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsCI");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsDERR");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsEcNoOffset");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsLAC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsMCC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsMNC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsRAC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsRNCid");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsScrCode");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsTxDiv");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsTxPwrRACH");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsCPICHTxPwr");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJSChangeOrigin");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjsSIB");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RtHopsIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("NrtHopsIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("HSDPAHopsIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RTWithHSDPAHopsIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    
		    i++;
		}
	}
	
	
	private void designExcelSheetHeaderADJI(Sheet wsheet,int i,Workbook wb) {
		if(i == 0){ // design header
			Row header = wsheet.createRow(0);
			Font headerFont = wb.createFont();
		    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		    CellStyle headerStyle = wb.createCellStyle();
		    headerStyle.setFont(headerFont);
		    headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		    headerStyle.setRotation((short) 90);
		    headerFont.setColor(IndexedColors.WHITE.getIndex());
		    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			int j = 0;
			
				
	
			
		    header.createCell(j).setCellValue("CELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("NCELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Parameter");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("PLMN");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RncId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WBTSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("LcrId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJIId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    
		    header.createCell(j).setCellValue("AdjiCI");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    									
		    													
		    header.createCell(j).setCellValue("AdjiCPICHTxPwr");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiLAC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiMCC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiMNC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiRAC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiRNCid");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		   
		    header.createCell(j).setCellValue("AdjiScrCode");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiSIB");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiTxDiv");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    								
		    header.createCell(j).setCellValue("AdjiTxPwrDPCH");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiTxPwrRACH");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiUARFCN");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("NrtHopiIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    					
		    header.createCell(j).setCellValue("RtHopiIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiComLoadMeasDRNCCellNCHO");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiHandlingBlockedCellSLHO");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjiNCHOHSPASupport");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("BlindHOTargetCell");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		   
		    i++;
		}
	}
	private void designExcelSheetHeaderADJG(Sheet wsheet,int i,Workbook wb) {
		if(i == 0){ // design header
			Row header = wsheet.createRow(0);
			Font headerFont = wb.createFont();
		    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		    CellStyle headerStyle = wb.createCellStyle();
		    headerStyle.setFont(headerFont);
		    headerStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
		    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		    headerStyle.setRotation((short) 90);
		    headerFont.setColor(IndexedColors.WHITE.getIndex());
		    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			int j = 0;
		    header.createCell(j).setCellValue("CELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("NCELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Parameter");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("PLMN");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RncId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WBTSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WCEL");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJG");									

		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgMCC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgMNC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJGChangeOrigin");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJGType");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgBCC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgBCCH");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgBandIndicator");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgCI");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgLAC");					
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgNCC");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgSIB");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgTxPwrMaxRACH");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("AdjgTxPwrMaxTCH");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("NrtHopgIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RtHopgIdentifier");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    i++;
		}
	}
	
	private void designExcelSheet(ADJSCells adjs,Sheet wsheet,int i,Workbook wb) {
		designExcelSheetHeader(wsheet, i,wb) ;
		i++;
		Row data = wsheet.createRow(i);
		// find param from WCEL and ADJS Table
		adjs.findWCELDataBySourceName();
		adjs.findADJSDataByDestName();

		// source
		int j = 0;
		data.createCell(j).setCellValue(adjs.getSource());
		j++;

		// destination
		data.createCell(j).setCellValue(adjs.getDestination());
		j++;
		// parameter
		data.createCell(j).setCellValue(adjs.getParameter());
		j++;
		// plmn
		data.createCell(j).setCellValue(adjs.getPLMN());
		j++;
		
		// RncId
		data.createCell(j).setCellValue(adjs.getRncId());
		j++;
		// WBTSId
		data.createCell(j).setCellValue(adjs.getWBTSId());
		j++;
		// LcrId
		data.createCell(j).setCellValue(adjs.getLcrId());
		j++;
		// ADJSId
		data.createCell(j).setCellValue(adjs.getADJSId());
		j++;

		// AdjsCI
		data.createCell(j).setCellValue(adjs.getAdjsCI());
		j++;
		// AdjsDERR
		data.createCell(j).setCellValue(adjs.getAdjsDERR());
		j++;
		// AdjsEcNoOffset
		data.createCell(j).setCellValue(adjs.getAdjsEcNoOffset());
		j++;
		// AdjsLAC
		data.createCell(j).setCellValue(adjs.getAdjsLAC());
		j++;

		// AdjsMCC
		data.createCell(j).setCellValue(adjs.getAdjsMCC());
		j++;
		// AdjsMNC
		data.createCell(j).setCellValue(adjs.getAdjsMNC());
		j++;

		// AdjsRAC
		data.createCell(j).setCellValue(adjs.getAdjsRAC());
		j++;

		// AdjsRNCid
		data.createCell(j).setCellValue(adjs.getAdjsRNCid());
		j++;

		// AdjsScrCode
		data.createCell(j).setCellValue(adjs.getAdjsScrCode());
		j++;

		// AdjsTxDiv
		data.createCell(j).setCellValue(adjs.getAdjsTxDiv());
		j++;
		// AdjsTxPwrRACH
		data.createCell(j).setCellValue(adjs.getAdjsTxPwrRACH());
		j++;

		// AdjsCPICHTxPwr
		data.createCell(j).setCellValue(adjs.getAdjsCPICHTxPwr());
		j++;
		// ADJSChangeOrigin
		data.createCell(j).setCellValue(adjs.getADJSChangeOrigin());
		j++;

		// AdjsSIB
	
		
		String sib ="";
		if(adjs.getParameter().contains("F1")||adjs.getParameter().contains("F2") ){
			if(adjs.getADJSId()!=null && !"".equals(adjs.getADJSId()) && !"MAX".equals(adjs.getADJSId()) && Integer.valueOf(adjs.getADJSId())<20)
				sib = "1";
			else
			if(adjs.getADJSId()!=null && !"".equals(adjs.getADJSId()) && !"MAX".equals(adjs.getADJSId()) && Integer.valueOf(adjs.getADJSId())>=20)
				sib = "2";
		}else
			if(adjs.getParameter().contains("F0")){
				if(adjs.getADJSId()!=null && !"".equals(adjs.getADJSId()) && !"MAX".equals(adjs.getADJSId()) && Integer.valueOf(adjs.getADJSId())<16)
					sib = "1";
				else
				if(adjs.getADJSId()!=null && !"".equals(adjs.getADJSId()) && !"MAX".equals(adjs.getADJSId()) && Integer.valueOf(adjs.getADJSId())>=16)
					sib = "2";
			}
			
		data.createCell(j).setCellValue(sib);
		j++;
		
		// RtHopsIdentifier
		data.createCell(j).setCellValue(adjs.getRtHopsIdentifier());
		j++;
		
		// RtHopsIdentifier
		data.createCell(j).setCellValue(adjs.getNrtHopsIdentifier());
		j++;
		
		// HSDPAHopsIdentifier
		data.createCell(j).setCellValue(adjs.getHSDPAHopsIdentifier());
		j++;
		// RTWithHSDPAHopsIdentifier
		data.createCell(j).setCellValue(adjs.getRTWithHSDPAHopsIdentifier());
	}
	
	private void designExcelSheetADJI(ADJICells adjs,Sheet wsheet,int i,Workbook wb) {
		designExcelSheetHeaderADJI(wsheet, i,wb) ;
		i++;
		Row data = wsheet.createRow(i);
		// find param from WCEL and ADJI Table
		adjs.findWCELDataBySourceName();
		adjs.findADJSDataByDestName();
		// source
		int j = 0;
		data.createCell(j).setCellValue(adjs.getSource());
		j++;
		// destination
		data.createCell(j).setCellValue(adjs.getDestination());
		j++;
		// parameter
		data.createCell(j).setCellValue(adjs.getParameter());
		j++;
		// plmn
		data.createCell(j).setCellValue(adjs.getPLMN());
		j++;
		

		// RncId
		data.createCell(j).setCellValue(adjs.getRncId());
		j++;
		// WBTSId
		data.createCell(j).setCellValue(adjs.getWBTSId());
		j++;
		// LcrId
		data.createCell(j).setCellValue(adjs.getLcrId());
		j++;
		// ADJSId
		data.createCell(j).setCellValue(adjs.getADJIId());
		j++;
									

		// AdjsCI
		data.createCell(j).setCellValue(adjs.getAdjiCI());
		j++;
		// AdjsDERR
		data.createCell(j).setCellValue(adjs.getAdjiCPICHTxPwr());
		j++;
		// AdjsEcNoOffset
		data.createCell(j).setCellValue(adjs.getAdjiLAC());
		j++;
		// AdjsLAC
		data.createCell(j).setCellValue(adjs.getAdjiMCC());
		j++;

		// AdjsMCC
		data.createCell(j).setCellValue(adjs.getAdjiMNC());
		j++;
		// AdjsMNC
		data.createCell(j).setCellValue(adjs.getAdjiRAC());
		j++;

		// AdjsRAC
		data.createCell(j).setCellValue(adjs.getAdjiRNCid());
		j++;

		data.createCell(j).setCellValue(adjs.getAdjiScrCode());
		j++;

		
		String sib ="";
		if(adjs.getParameter().contains("F1")||adjs.getParameter().contains("F2") ){
			if(adjs.getADJIId()!=null && !"".equals(adjs.getADJIId()) && !"MAX".equals(adjs.getADJIId()) && Integer.valueOf(adjs.getADJIId())<20)
				sib = "1";
			else
			if(adjs.getADJIId()!=null && !"".equals(adjs.getADJIId()) && !"MAX".equals(adjs.getADJIId()) && Integer.valueOf(adjs.getADJIId())>=20)
				sib = "2";
		}else
			if(adjs.getParameter().contains("F0")){
				if(adjs.getADJIId()!=null && !"".equals(adjs.getADJIId()) && !"MAX".equals(adjs.getADJIId()) && Integer.valueOf(adjs.getADJIId())<16)
					sib = "1";
				else
				if(adjs.getADJIId()!=null && !"".equals(adjs.getADJIId()) && !"MAX".equals(adjs.getADJIId()) && Integer.valueOf(adjs.getADJIId())>=16)
					sib = "2";
			}
			
		data.createCell(j).setCellValue(sib);
		j++;		
		

		// AdjsScrCode
		data.createCell(j).setCellValue(adjs.getAdjiTxDiv());
		j++;

		// AdjsTxDiv
		data.createCell(j).setCellValue(adjs.getAdjiTxPwrDPCH());
		j++;
		// AdjsTxPwrRACH
		data.createCell(j).setCellValue(adjs.getAdjiTxPwrRACH());
		j++;

		// AdjsCPICHTxPwr
		data.createCell(j).setCellValue(adjs.getAdjiUARFCN());
		j++;
		// ADJSChangeOrigin
		data.createCell(j).setCellValue(adjs.getNrtHopiIdentifier());
		j++;
		// RtHopsIdentifier
		data.createCell(j).setCellValue(adjs.getRtHopiIdentifier());
		j++;
		
		// RtHopsIdentifier
		data.createCell(j).setCellValue(adjs.getAdjiComLoadMeasDRNCCellNCHO());
		j++;
		
		// HSDPAHopsIdentifier
		data.createCell(j).setCellValue(adjs.getAdjiHandlingBlockedCellSLHO());
		j++;
		// RTWithHSDPAHopsIdentifier
		data.createCell(j).setCellValue(adjs.getAdjiNCHOHSPASupport());
		j++;
		data.createCell(j).setCellValue(adjs.getBlindHOTargetCell());
		j++;
		
	}
	
	private void designExcelSheetADJG(ADJGCells adjs,Sheet wsheet,int i,Workbook wb) {
		designExcelSheetHeaderADJG(wsheet, i,wb) ;
		i++;
		Row data = wsheet.createRow(i);
		// find param from WCEL and ADJS Table
		adjs.findWCELDataBySourceName();
		adjs.findADJGDataByDestName();
		// source
		int j = 0;
		data.createCell(j).setCellValue(adjs.getSource());
		j++;

		// destination
		data.createCell(j).setCellValue(adjs.getDestination());
		j++;
		// parameter
		data.createCell(j).setCellValue(adjs.getParameter());
		j++;
		// plmn
		data.createCell(j).setCellValue(adjs.getPLMN());
		j++;
	

		// RncId
		data.createCell(j).setCellValue(adjs.getRNC());
		j++;
		// WBTSId
		data.createCell(j).setCellValue(adjs.getWBTS());
		j++;
		// LcrId
		data.createCell(j).setCellValue(adjs.getLcrId());
		j++;
		// ADJSId
		data.createCell(j).setCellValue(adjs.getADJGId());						

		j++;

		// AdjsCI
		data.createCell(j).setCellValue(adjs.getAdjgMCC());
		j++;
		// AdjsDERR
		data.createCell(j).setCellValue(adjs.getAdjgMNC());
		j++;
		// AdjsEcNoOffset
		data.createCell(j).setCellValue(adjs.getADJGChangeOrigin());
		j++;
		// AdjsLAC
		data.createCell(j).setCellValue(adjs.getADJGType());
		j++;

		// AdjsMCC
		data.createCell(j).setCellValue(adjs.getAdjgBCC());
		j++;
		// AdjsMNC
		data.createCell(j).setCellValue(adjs.getAdjgBCCH());
		j++;

		// AdjsRAC
		data.createCell(j).setCellValue(adjs.getAdjgBandIndicator());								
		j++;

		// AdjsRNCid
		data.createCell(j).setCellValue(adjs.getAdjgCI());
		j++;

		// AdjsScrCode
		data.createCell(j).setCellValue(adjs.getAdjgLAC());
		j++;

		// AdjsTxDiv
		data.createCell(j).setCellValue(adjs.getAdjgNCC());
		j++;
		// AdjsTxPwrRACH
		data.createCell(j).setCellValue(adjs.getAdjgSIB());
		j++;

		// AdjsCPICHTxPwr
		data.createCell(j).setCellValue(adjs.getAdjgTxPwrMaxRACH());
		j++;
		// ADJSChangeOrigin
		data.createCell(j).setCellValue(adjs.getAdjgTxPwrMaxTCH());
		j++;
		
		data.createCell(j).setCellValue(adjs.getNrtHopgIdentifier());
		j++;
		
		data.createCell(j).setCellValue(adjs.getRtHopgIdentifier());
		j++;

	
	}
	
	private void designExcelSheetToDeleteHeader(Sheet wsheet,int i,Workbook wb) {
		if(i == 0){ // design header
			
			Row header = wsheet.createRow(0);
			Font headerFont = wb.createFont();
		    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		    CellStyle headerStyle = wb.createCellStyle();
		    headerStyle.setFont(headerFont);
		    headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		    headerStyle.setRotation((short) 90);
		    headerFont.setColor(IndexedColors.WHITE.getIndex());
		    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			int j = 0;
		    header.createCell(j).setCellValue("CELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RncId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WBTSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("LcrId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Target WCEL Name");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ATT");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Avg CPICH Ec/No");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("CPICH RSCP SHO");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Distance with Source");
		    header.getCell(j).setCellStyle(headerStyle);
		    
		    i++;
		    
		}
	}
	private void designExcelSheetToDeleteHeaderADJI(Sheet wsheet,int i,Workbook wb) {
		if(i == 0){ // design header
			
			Row header = wsheet.createRow(0);
			Font headerFont = wb.createFont();
		    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		    CellStyle headerStyle = wb.createCellStyle();
		    headerStyle.setFont(headerFont);
		    headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		    headerStyle.setRotation((short) 90);
		    headerFont.setColor(IndexedColors.WHITE.getIndex());
		    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			int j = 0;
		    header.createCell(j).setCellValue("CELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RncId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WBTSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("LcrId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Target WCEL Name");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ATT");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Avg CPICH Ec/No");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Avg CPICH RSCP");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Distance with Source");
		    header.getCell(j).setCellStyle(headerStyle);
		    i++;
		    
		}
	}
	private void designExcelSheetToDeleteHeaderADJG(Sheet wsheet,int i,Workbook wb) {
		if(i == 0){ // design header
			
			Row header = wsheet.createRow(0);
			Font headerFont = wb.createFont();
		    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		    CellStyle headerStyle = wb.createCellStyle();
		    headerStyle.setFont(headerFont);
		    headerStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
		    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		    headerStyle.setRotation((short) 90);
		    headerFont.setColor(IndexedColors.WHITE.getIndex());
		    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			int j = 0;
		    header.createCell(j).setCellValue("CELL ID");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("RncId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("WBTSId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("LcrId");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ADJG");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("Target WCEL Name");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
		    header.createCell(j).setCellValue("ATT");
		    header.getCell(j).setCellStyle(headerStyle);
		    j++;
//		    header.createCell(j).setCellValue("Avg CPICH Ec/No");
//		    header.getCell(j).setCellStyle(headerStyle);
//		    j++;
//		    header.createCell(j).setCellValue("CPICH RSCP SHO");
//		    header.getCell(j).setCellStyle(headerStyle);
//		    j++;
		    header.createCell(j).setCellValue("Distance with Source");
		    header.getCell(j).setCellStyle(headerStyle);
		    i++;
		    
		}
	}
	private void designExcelSheetToDelete(ADJSCells adjs,Sheet wsheet,int i,Rapport rapport,HttpServletRequest request,Workbook wb) {
		

		designExcelSheetToDeleteHeader(wsheet, i,wb) ;
		int j = 0;
		i++;
		Row data = wsheet.createRow(i);
		// source
		data.createCell(j).setCellValue(adjs.getSource());
		j++;
		// RncId
		data.createCell(j).setCellValue(adjs.getRncId());
		j++;
		// WBTSId
		data.createCell(j).setCellValue(adjs.getWBTSId());
		j++;
		//LCR ID
		data.createCell(j).setCellValue(adjs.getLcrId());
		j++;
		// ADJSId
		data.createCell(j).setCellValue(adjs.getADJSId());
		j++;
		// Target
		data.createCell(j).setCellValue(rapport.getTarget_WCEL_Name());
		j++;

		// ATT
		data.createCell(j).setCellValue(rapport.getAtt()!=null?rapport.getAtt():"0");
		j++;
		
	
		//Avg CPICH Ec/No
		data.createCell(j).setCellValue(rapport.getAvg_CPICH()!=null?rapport.getAvg_CPICH():"0");
		j++;
	
		
		//CPICH RSCP SHO	
		data.createCell(j).setCellValue(rapport.getEc_No_CPICH_RSCP_SHO()!=null?rapport.getEc_No_CPICH_RSCP_SHO():"0");
		j++;
		
		//getDistanceWithSource
		data.createCell(j).setCellValue(rapport.getDistanceWithSource()!=null?rapport.getDistanceWithSource():0);
		j++;
		
		
		

	}
	
	
private void designExcelSheetToDeleteADJI(ADJICells adjs,Sheet wsheet,int i,RapportADJI rapport,HttpServletRequest request,Workbook wb) {

		designExcelSheetToDeleteHeaderADJI(wsheet, i,wb) ;
		int j = 0;
		i++;
		Row data = wsheet.createRow(i);
		// source
		data.createCell(j).setCellValue(adjs.getSource());
		j++;
		// RncId
		data.createCell(j).setCellValue(adjs.getRncId());
		j++;
		// WBTSId
		data.createCell(j).setCellValue(adjs.getWBTSId());
		j++;
		//LCR ID
		data.createCell(j).setCellValue(adjs.getLcrId());
		j++;
		// ADJSId
		data.createCell(j).setCellValue(adjs.getADJIId());
		j++;
		// Target
		data.createCell(j).setCellValue(rapport.getTarget_WCEL_Name());
		j++;

		// ATT
		data.createCell(j).setCellValue(rapport.getAtt()!=null?rapport.getAtt():"0");
		j++;
	
		//Avg CPICH Ec/No
		data.createCell(j).setCellValue(rapport.getAvg_CPICH()!=null?rapport.getAvg_CPICH():"0");
		j++;

		//CPICH RSCP SHO	
		data.createCell(j).setCellValue(rapport.getAvg_CPICH_RSCP()!=null?rapport.getAvg_CPICH_RSCP():"0");
		j++;
		
		//getDistanceWithSource
		data.createCell(j).setCellValue(rapport.getDistanceWithSource()!=null?rapport.getDistanceWithSource():0);
		j++;
	}
	private void designExcelSheetToDeleteADJG(ADJGCells adjs,Sheet wsheet,int i,RapportADJG rapport,HttpServletRequest request,Workbook wb) {
		

		designExcelSheetToDeleteHeaderADJG(wsheet, i,wb) ;
		int j = 0;
		i++;
		Row data = wsheet.createRow(i);
		// source
		data.createCell(j).setCellValue(adjs.getSource());
		j++;
		// RncId
		data.createCell(j).setCellValue(adjs.getRNC());
		j++;
		// WBTSId
		data.createCell(j).setCellValue(adjs.getWBTS());
		j++;
		//LCR ID
		data.createCell(j).setCellValue(adjs.getLcrId());
		j++;
		// ADJSId
		data.createCell(j).setCellValue(adjs.getADJGId());
		j++;
		// Target
		data.createCell(j).setCellValue(rapport.getTarget_Segment_Name());
		j++;

		// ATT
		data.createCell(j).setCellValue(rapport.getAtt()!=null?rapport.getAtt():"0");
		j++;
		
	
//		//Avg CPICH Ec/No
//		data.createCell(j).setCellValue(rapport.getAvg_CPICH()!=null?rapport.getAvg_CPICH():0);
//		j++;
//	
//		
//		//CPICH RSCP SHO	
//		data.createCell(j).setCellValue(rapport.getEc_No_CPICH_RSCP_SHO()!=null?rapport.getEc_No_CPICH_RSCP_SHO():0);
//		j++;
		
		//getDistanceWithSource
		data.createCell(j).setCellValue(rapport.getDistanceWithSource()!=null?rapport.getDistanceWithSource():0);
		j++;
		
		
		

	}
	
	private void applyTemplate(ADJSCells adjs){
		int i = 0;
		for(Template temp:lstTemplate){
			if(temp.getName().equalsIgnoreCase(adjs.getParameter())){
				Template template = lstTemplate.get(i);
				adjs.setAdjsDERR(""+template.getAdjsDERR());
				adjs.setAdjsEcNoOffset(""+template.getAdjsEcNoOffset());
				adjs.setAdjsTxPwrRACH(""+template.getAdjsTxPwrRACH());
				adjs.setADJSChangeOrigin(""+template.getADJSChangeOrigin());
				adjs.setRtHopsIdentifier(""+template.getRtHopsIdentifier());
				adjs.setNrtHopsIdentifier(""+template.getNrtHopsIdentifier());
				adjs.setHSDPAHopsIdentifier(""+template.getHSDPAHopsIdentifier());
				adjs.setRTWithHSDPAHopsIdentifier(""+template.getRTWithHSDPAHopsIdentifier());
				break;
			}	
			i++;
		}
	}
	
	private void applyTemplateADJI(ADJICells adji){
		int i = 0;
		for(TemplateADJI temp:lstTemplateADJI){
			if(temp.getName().equalsIgnoreCase(adji.getParameter())){
				TemplateADJI template = lstTemplateADJI.get(i);
				adji.setAdjiCPICHTxPwr(""+template.getAdjiCPICHTxPwr());
				adji.setAdjiTxDiv(""+template.getAdjiTxDiv());
				adji.setAdjiTxPwrDPCH(""+template.getAdjiTxPwrDPCH());
				adji.setAdjiTxPwrRACH(""+template.getAdjiTxPwrRACH());
				adji.setNrtHopiIdentifier(""+template.getNrtHopiIdentifier());
				adji.setRtHopiIdentifier(""+template.getRtHopiIdentifier());
				adji.setAdjiComLoadMeasDRNCCellNCHO(""+template.getAdjiComLoadMeasDRNCCellNCHO());
				adji.setAdjiHandlingBlockedCellSLHO(""+template.getAdjiHandlingBlockedCellSLHO());
				adji.setAdjiNCHOHSPASupport(""+template.getAdjiNCHOHSPASupport());
				adji.setBlindHOTargetCell(""+template.getBlindHOTargetCell());
				adji.setParameter(""+template.getName());
				break;
			}	
			i++;
		}
	}
	
	private void applyTemplateADJG(ADJGCells adjg){
		int i = 0;
		for(TemplateADJG temp:lstTemplateADJG){
			if(temp.getName().equalsIgnoreCase(adjg.getParameter())){
				TemplateADJG template = lstTemplateADJG.get(i);
				adjg.setAdjgMCC(""+template.getAdjgMCC());
				adjg.setAdjgMNC(""+template.getAdjgMNC());
				adjg.setADJGChangeOrigin(""+template.getADJGChangeOrigin());
				adjg.setADJGType(""+template.getADJGType());
				adjg.setAdjgBandIndicator(""+template.getAdjgBandIndicator());
				adjg.setAdjgTxPwrMaxRACH(""+template.getAdjgTxPwrMaxRACH());
				adjg.setAdjgTxPwrMaxTCH(""+template.getAdjgTxPwrMaxTCH());
				adjg.setNrtHopgIdentifier(""+template.getNrtHopgIdentifier());
				adjg.setRtHopgIdentifier(""+template.getRtHopgIdentifier());
				adjg.setPLMN(template.getName());
				break;
			}	
			i++;
		}
	}
	
	
	private void readRapports(ADJSCellsGroup group){
		Sheet sheet = rapport.getSheet("ADJS");
		
		for (int i = 1; i < sheet.getLastRowNum(); i++) {
			String sourceName = getCellValue(sheet.getRow(i).getCell(3));
			String neighbor =getCellValue(sheet.getRow(i).getCell(12));
			String targetName = getCellValue(sheet.getRow(i).getCell(9));
			group.setGpsData(InitContext.gps.get(sourceName));
			if(group.getSource().equals(sourceName) && "Yes".equalsIgnoreCase(neighbor) && ! areCosite(sourceName,targetName)){
				Rapport rapport = new Rapport();
				int col = 0;
				rapport.setPLMN(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_RNC_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WBTS_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_adjacencies(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_LCR_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_RNC_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WBTS_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WCEL_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WCEL_LCR_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WCEL_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setNeighbor(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setAtt(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setShare(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSR_per_adjacency(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setEc_No_diff_btw_cell_pair(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setAvg_CPICH (getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setEc_No_CPICH_RSCP_SHO(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setGpsData(InitContext.gps.get(rapport.getTarget_WCEL_Name()));
				if(rapport.getGpsData()!=null)
					rapport.setDistanceWithSource(Utils.distance(group.getGpsData().getLatitude(), group.getGpsData().getLongitute(), rapport.getGpsData().getLatitude(),  rapport.getGpsData().getLongitute()));
				group.addRapport(rapport);
			}else 
				continue;
			
		}
		System.out.println(group.getSource());
	}
	
	private void readRapportsADJI(ADJICellsGroup group){
		Sheet sheet = rapport.getSheet("ADJI");
		
		for (int i = 1; i < sheet.getLastRowNum(); i++) {
			String sourceName = getCellValue(sheet.getRow(i).getCell(3));
			String neighbor =getCellValue(sheet.getRow(i).getCell(12));
			String targetName = getCellValue(sheet.getRow(i).getCell(9));
			group.setGpsData(InitContext.gps.get(sourceName));
			if(group.getSource().equals(sourceName) && "Yes".equalsIgnoreCase(neighbor) && ! areCosite(sourceName,targetName)){
				RapportADJI rapport = new RapportADJI();
				int col = 0;
				rapport.setPLMN(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_RNC_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WBTS_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_adjacencies(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_LCR_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_RNC_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WBTS_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WCEL_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WCEL_LCR_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_WCEL_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setNeighbor(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setAtt(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setShare(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSR_per_adjacency(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setAvg_CPICH(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setAvg_CPICH_RSCP(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setGpsData(InitContext.gps.get(rapport.getTarget_WCEL_Name()));
				if(rapport.getGpsData()!=null)
					rapport.setDistanceWithSource(Utils.distance(group.getGpsData().getLatitude(), group.getGpsData().getLongitute(), rapport.getGpsData().getLatitude(),  rapport.getGpsData().getLongitute()));
				group.addRapport(rapport);
			}else 
				continue;
			
		}
		System.out.println(group.getSource());
	}
	
	private void readRapportsADJG(ADJGCellsGroup group){
		Sheet sheet = rapport.getSheet("ADJG");
		for (int i = 1; i < sheet.getLastRowNum(); i++) {
			String sourceName = getCellValue(sheet.getRow(i).getCell(3));
			String neighbor =getCellValue(sheet.getRow(i).getCell(11));
			String targetName = getCellValue(sheet.getRow(i).getCell(7));
			group.setGpsData(InitContext.gps.get(sourceName));
			if(group.getSource().equals(sourceName) && "Yes".equalsIgnoreCase(neighbor) && ! areCosite(sourceName,targetName)){
				RapportADJG rapport = new RapportADJG();
				int col = 0;
				rapport.setPLMN_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_RNC_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WBTS_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_adjacencies(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_LCR_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSource_WCEL_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_Segment_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_BTS_Name(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_LAC_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setTarget_CELL_ID(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setNeighbor(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setAtt(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setShare(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				rapport.setSR_per_adjacency(getCellValue(sheet.getRow(i).getCell(col)));
				col++;
				System.out.println(getCellValue(sheet.getRow(i).getCell(col)));
				rapport.setAvg_GSM_RSSI((getCellValue(sheet.getRow(i).getCell(col))));
				col++;
				rapport.setAvg_BSIC_verif_time((getCellValue(sheet.getRow(i).getCell(col))));
				col++;
				rapport.setGpsData(InitContext.gps.get(rapport.getTarget_Segment_Name()));
				if(rapport.getGpsData()!=null)
					rapport.setDistanceWithSource(Utils.distance(group.getGpsData().getLatitude(), group.getGpsData().getLongitute(), rapport.getGpsData().getLatitude(),  rapport.getGpsData().getLongitute()));
				group.addRapport(rapport);
			}else 
				continue;
			
		}
		System.out.println(group.getSource());
	}
	
	private boolean areCosite(String source,String target){
		String sourceCosite = source.substring(0,3) + source.substring(4,7);
		String targetCosite = target.substring(0,3) + target.substring(4,7);
		if(sourceCosite.equalsIgnoreCase(targetCosite)){
			System.out.println("Cosite found in Source " + source +" and Target = "+target);
			return true;
		}
		return false;
	}
	
	private  String getCellValue(Cell cell) {
		if(cell!=null){
			cell.setCellType(Cell.CELL_TYPE_STRING);
			return cell.getStringCellValue();
		}
		return "";
	}

}
